package fischl;

import fischl.log.Log;
import fischl.log.LogFactory;
import fischl.model.VideoModel;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.Set;

public final class VideoMarker {
  private static final Log log = LogFactory.get("sqlMarker");
  private static Connection connection;
  private static Set<String> ids;

  public static void mark(VideoModel model) {
    log.i("marking vid {}, upId {}, upName {}", model.vid, model.upId, model.upName);
    ids.add(model.vid);
    try {
      Statement statement = connection.createStatement();
      long millis = System.currentTimeMillis();
      String sql = String.format("insert into marker(vid, videoTime, markTime) values('%s', '%d'," +
        " '%d');", model.vid, millis, millis);
      int update = statement.executeUpdate(sql);
      if (update != 1) {
        log.e("Unable to update marker");
      }
    } catch (Exception e) {
      log.e(e);
    }

  }

  public static Set<String> getAllMarkedIds() {
    if (ids != null) return Collections.unmodifiableSet(ids);
    ids = new LinkedHashSet<>();
    try {
      Statement statement = connection.createStatement();
      ResultSet set = statement.executeQuery("select * from marker;");
      while (set.next()) {
        ids.add(set.getString(1));
      }
      set.close();
      statement.close();
    } catch (Exception e) {
      log.e(e);
    }
    log.i("{} markers found", ids.size());
    return ids;
  }

  static {
    init();
  }

  private static void init() {
    try {
      String home = System.getProperty("user.home");
      connection = DriverManager.getConnection("jdbc:sqlite:" + home + "/fischl.db");
      Statement statement = connection.createStatement();
      ResultSet set = statement.executeQuery("select * from sqlite_master where type = " +
        "'table' and name = 'marker';");
      boolean tableCreated = set.next();
      set.close();
      if (!tableCreated) {
        log.i("table marker does not exists, creating...");
        statement.execute("create table marker(vid text not null, videoTime int not null, " +
          "markTime int not null);");
      } else {
        log.i("table marker already exists, create skipped");
      }
      log.i("deleting obsolete entries...");
      long time = System.currentTimeMillis() - 3L * 24 * 60 * 60 * 1000;
      int deleteCount =
        statement.executeUpdate("delete from marker where markTime < " + time + ";");
      if (deleteCount >= 0) {
        log.i("{} rows deleted by markTime < {}", deleteCount, time);
      }
      statement.close();
    } catch (SQLException e) {
      log.e(e);
    }
  }
}